package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.config.annotation.DataScope;
import com.dragon.repository.entity.Expenses;
import com.dragon.repository.entity.Reimbursement;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 报销申请表 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-07-03
 */
@Mapper
public interface ReimbursementMapper extends BaseMapper<Reimbursement> {

    @Select("SELECT M.* FROM " +
            "(SELECT a.*, c.username AS applicantName FROM dragon_reimbursement a " +
            "LEFT JOIN dragon_admin c ON a.admin_id = c.id) M where M.id = #{id}  " )
    Reimbursement selectByReimburId(Integer id);

    /**
     * 报销 月
     * @return
     */
    @Select("SELECT SUM(a.reimburse_money) FROM dragon_reimbursement a WHERE a.status = 1 and LEFT(a.create_time,7) = LEFT(NOW(),7)")
    @DataScope(adminAlias = "a", adminField = "admin_id", deptAlias = "a", deptField = "department_id", authCodeString = "reimburseManagement")
    Double selectMonthReimburseMoney();

    /**
     * 报销 总
     * @return
     */
    @Select("SELECT SUM(a.reimburse_money) FROM dragon_reimbursement a WHERE a.status = 1  ")
    @DataScope(adminAlias = "a", adminField = "admin_id", deptAlias = "a", deptField = "department_id", authCodeString = "reimburseManagement")
    Double selectYearReimburseMoney();

    @Select("<script>" +
            "SELECT M.* from (SELECT a.*,aa.username as applicantName, e.name as expensesName  FROM dragon_reimbursement a " +
            "left join dragon_admin aa on a.admin_id = aa.id " +
            "left join dragon_expenses e on a.reimburse_type = e.id )M " +
            "${ew.customSqlSegment} " +
            "</script>")
    @DataScope(adminAlias = "M", adminField = "admin_id", deptAlias = "M", deptField = "department_id" , authCodeString = "reimburseManagement")
    Page<Reimbursement> selectListBySearch(Page<Reimbursement> rePage, @Param(Constants.WRAPPER) QueryWrapper<Reimbursement> wrapper);

    /**
     * 报销 年金额
     * @param year
     * @return
     */
    @Select("SELECT SUM(a.reimburse_money) FROM dragon_reimbursement a WHERE a.status = 1 and LEFT(a.create_time,4) = #{year}")
    @DataScope(adminAlias = "a", adminField = "admin_id", deptAlias = "a", deptField = "department_id", authCodeString = "reimburseManagement")
    Double selectMoneyForYear(String year);


    @Select("with base as(" +
            "    select reimburse_money money,create_time from dragon_reimbursement where status = 1" +
            ")," +
            "totalMoney as (" +
            "    select sum(money) totalReimburseMoney from base" +
            ")," +
            "yearMoney as (" +
            "    select sum(money) yearReimburseMoney from base where left(create_time,4) = left(now(),4)" +
            ")," +
            "monthMoney as (" +
            "    select sum(money) monthReimburseMoney from base where left(create_time,7) = left(now(),7)" +
            ")," +
            "makeData as (" +
            "    select a.totalReimburseMoney,b.yearReimburseMoney,c.monthReimburseMoney from totalMoney a,yearMoney b,monthMoney c" +
            ")" +
            "select * from makeData")
    Map<String,Object> selectListStatistics();

    /**
     * 报销类型占比
     * @param time
     * @return
     */
    @Select("SELECT " +
            "  de.id, de.name, " +
            "  IFNULL(SUM(dr.reimburse_money),0) AS reimburseTypeMoney, " +
            "  IFNULL( ROUND(SUM(dr.reimburse_money) / (SELECT SUM(reimburse_money) FROM dragon_reimbursement where status = 1 and YEAR(create_time) = #{time}) * 100, 1) ,0)  AS reimburseTypeAvg " +
            "from dragon_expenses de " +
            "LEFT JOIN dragon_reimbursement dr on dr.reimburse_type = de.id and dr.status = 1 and YEAR(dr.create_time) = #{time} " +
            "GROUP BY de.id ")
    List<Expenses> reimburseTypePercentage(String time);

    /**
     * 报销月总额统计
     * @param time
     * @return
     */
    @Select("SELECT " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '1' THEN dr.reimburse_money ELSE 0 END ),0) AS '1月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '2' THEN dr.reimburse_money ELSE 0 END ),0) AS '2月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '3' THEN dr.reimburse_money ELSE 0 END ),0) AS '3月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '4' THEN dr.reimburse_money ELSE 0 END ),0) AS '4月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '5' THEN dr.reimburse_money ELSE 0 END ),0) AS '5月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '6' THEN dr.reimburse_money ELSE 0 END ),0) AS '6月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '7' THEN dr.reimburse_money ELSE 0 END ),0) AS '7月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '8' THEN dr.reimburse_money ELSE 0 END ),0) AS '8月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '9' THEN dr.reimburse_money ELSE 0 END ),0) AS '9月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '10' THEN dr.reimburse_money ELSE 0 END ),0) AS '10月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '11' THEN dr.reimburse_money ELSE 0 END ),0) AS '11月', " +
            "IFNULL(sum( CASE MONTH ( dr.create_time ) WHEN '12' THEN dr.reimburse_money ELSE 0 END ),0) AS '12月' " +
            "FROM dragon_reimbursement dr " +
            "WHERE dr.status = 1 AND YEAR( dr.create_time ) = #{time} ")
    LinkedHashMap<String, Object> reimburseMonthlyStatistics(String time);
}
